CREATE   PROC [dbo].[asi_CheckImisNetPrivilegeLevel]
    @userId as varchar(60),
    @systemKeyword as varchar(25)
    AS
BEGIN

set nocount on

declare @userKey as uniqueidentifier
declare @accessKey as uniqueidentifier
declare @permissions as int
declare @tmppermission as int

declare @LevelNet as int

declare @EveryoneRoleKey uniqueidentifier
select @EveryoneRoleKey = ParameterValue from SystemConfig
    where ParameterName = 'Security.Token.EveryoneRoleKey'

select @userKey = UserKey from UserMain
where UserId = @userId

select @accessKey = ApplicationAccessKey
from SystemEntity
where SystemKeyword = @systemKeyword

CREATE TABLE #permission (permission int)

-- Add Self
INSERT    #permission(permission)
select Permission from AccessItem
where AccessKey = @accessKey
and UserKey = @userKey

-- Everyone Role
INSERT    #permission(permission)
select Permission from AccessItem
where AccessKey = @accessKey
and RoleKey = @EveryoneRoleKey

-- Assigned Roles
INSERT    #permission(permission)
select Permission from AccessItem
where AccessKey = @accessKey
and RoleKey in (SELECT RoleKey FROM UserRole WHERE UserKey = @userKey
    AND RoleKey <> @EveryoneRoleKey)

-- Effective Group Membership
INSERT    #permission(permission)
select Permission from AccessItem
where AccessKey = @accessKey
and GroupKey in (SELECT    DISTINCT GroupKey
FROM    GroupMember
WHERE    MemberContactKey = @userKey
   AND    (JoinDate <= getdate() OR JoinDate IS NULL)
   AND    (DropDate >= getdate() OR DropDate IS NULL))

set @permissions = 0
DECLARE Permission_Cursor CURSOR FOR
SELECT permission from #permission
OPEN Permission_Cursor
FETCH NEXT FROM Permission_Cursor
INTO @tmppermission
set @permissions = @permissions | @tmppermission
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM Permission_Cursor
    INTO @tmppermission
    set @permissions = @permissions | @tmppermission
END
CLOSE Permission_Cursor
DEALLOCATE Permission_Cursor

IF (@permissions & 4096) > 0
begin
    select 3 as LevelNet
    set @LevelNet = 3
end
ELSE IF (@permissions & 2048) > 0
begin
    select 2 as LevelNet
    set @LevelNet = 2
end
ELSE IF (@permissions & 1024) > 0
begin
    select 1 as LevelNet
    set @LevelNet = 1
end
ELSE
begin
    select 0 as LevelNet
    set @LevelNet = 0
end

drop table #permission

return @LevelNet

set nocount off

END

GO
GRANT EXECUTE ON  [dbo].[asi_CheckImisNetPrivilegeLevel] TO [IMIS]
GO